﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PSE.Framework.ProviderLoader;
using PSE.Framework.ProviderLoader.Configuration;
using PSE.AuditLogging.Repository;
using System.Data.Common;
using Sinacor.AuditLogging.LogEntry;
using System.Xml.Serialization;
using System.Diagnostics;
using System.Data;
using Sinacor.Infra.Service.Data;
using PSE.AuditLogging.LogEntry;

namespace Sinacor.AuditLogging.Repository.Providers
{
    public class SinacorGenericRepository : BaseProvider, IContingencyRepository, IPersistentRepository
    {
        public SinacorGenericRepository(ProviderConfigurationElement configuration)
            : base(configuration)
        {
        }

        #region IContingencyRepository Members

        /// <summary>
        /// Ler uma entrada de log do repositório
        /// </summary>
        /// <returns></returns>
        public PSE.AuditLogging.LogEntry.LogEntryPackage Peek()
        {
            //Metodo nao implementado por nao ser suportado por esse provider
            return null;
        }

        /// <summary>
        /// Ler e retirnar uma entrada de log do repositório
        /// </summary>
        /// <returns></returns>
        public PSE.AuditLogging.LogEntry.LogEntryPackage Receive()
        {
            //Metodo nao implementado por nao ser suportado por esse provider
            return null;
        }

        /// <summary>
        /// Remover a entrada de log mais antiga do repositório
        /// </summary>
        public void RemoveTop()
        {
            //Metodo nao implementado por nao ser suportado por esse provider
            return;
        }
        #endregion

        #region IPersistentRepository Members

        /// <summary>
        /// Enviar uma entrada de log para o repositório
        /// </summary>
        /// <param name="entryPackage"></param>
        /// 
        public void Send(LogEntryPackage entryPackage)
        {
            this.Send(entryPackage, true);
        }

        /// <summary>
        /// Enviar uma entrada de log não assinada ou com assinatura inválida para o repositório
        /// </summary>
        /// <param name="entry"></param>
        public void SendUnsigned(PSE.AuditLogging.LogEntry.LogEntryPackage entryPackage)
        {
            this.Send(entryPackage, false);
        }

        private void Send(LogEntryPackage entryPackage, bool signed)
        {
            SinacorGenericLogEntry logEntry = entryPackage.Body as SinacorGenericLogEntry;
            if (logEntry == null)
                throw new ArgumentException("Invalid log entry package body type.");

            using (OracleDataBase dataBase = new OracleDataBase())
            {
                using (DbConnection connection = dataBase.CreateConnection())
                {
                    DbTransaction trans = null;
                    DbCommand command1 = null;
                    DbCommand command2 = null;
                    DbCommand command3 = null;

                    try
                    {
                        connection.Open();

                        trans = connection.BeginTransaction();

                        int logTypeId = CreateOrRetrieveLogTypeId(dataBase, connection, trans, logEntry.LogType);
                        int logSubTypeId = CreateOrRetrieveLogSubTypeId(dataBase, connection, trans, logEntry.LogSubType);

                        command1 = connection.CreateCommand();
                        command2 = connection.CreateCommand();
                        command3 = connection.CreateCommand();

                        #region Recupera chave do header de log
                        command1.CommandText = "SELECT STFWHEAD_LOG_AUDT.NEXTVAL FROM DUAL";
                        command1.Transaction = trans;

                        int codigoHeader = Convert.ToInt32(command1.ExecuteScalar());

                        #endregion

                        #region Inserir registro na tabela de header
                        command2.Transaction = trans;
                        command2.CommandText = " INSERT INTO TFWHEAD_LOG_AUDT";
                        command2.CommandText += " (COD_HEAD_LOG_AUDT,DATA_HORA_GERA_LOG,NOME_ETRB,CD_USUARIO,CD_EMPRESA,CD_SISTEMA,COD_TIPO_LOG,COD_STIP_LOG,IND_CNTD_ASS)";
                        command2.CommandText += " VALUES (:COD_HEAD_LOG_AUDT,:DATA_HORA_GERA_LOG,:NOME_ETRB,:CD_USUARIO,:CD_EMPRESA,:CD_SISTEMA,:COD_TIPO_LOG,:COD_STIP_LOG,:IND_CNTD_ASS)";

                        dataBase.AddInParameter(command2, "COD_HEAD_LOG_AUDT", DbType.Int32);
                        dataBase.SetParameterValue(command2, "COD_HEAD_LOG_AUDT", codigoHeader);

                        dataBase.AddInParameter(command2, "DATA_HORA_GERA_LOG", DbType.DateTime);
                        dataBase.SetParameterValue(command2, "DATA_HORA_GERA_LOG", logEntry.Timestamp);

                        dataBase.AddInParameter(command2, "NOME_ETRB", DbType.String);
                        dataBase.SetParameterValue(command2, "NOME_ETRB", logEntry.SourceMachine);

                        dataBase.AddInParameter(command2, "CD_USUARIO", DbType.Int32);
                        dataBase.SetParameterValue(command2, "CD_USUARIO", logEntry.UserId);

                        dataBase.AddInParameter(command2, "CD_EMPRESA", DbType.Int32);
                        dataBase.SetParameterValue(command2, "CD_EMPRESA", logEntry.CompanyId);

                        dataBase.AddInParameter(command2, "CD_SISTEMA", DbType.String);
                        dataBase.SetParameterValue(command2, "CD_SISTEMA", logEntry.ModuleId);

                        dataBase.AddInParameter(command2, "COD_TIPO_LOG", DbType.Int32);
                        dataBase.SetParameterValue(command2, "COD_TIPO_LOG", logTypeId);

                        dataBase.AddInParameter(command2, "COD_STIP_LOG", DbType.Int32);
                        dataBase.SetParameterValue(command2, "COD_STIP_LOG", logSubTypeId);

                        dataBase.AddInParameter(command2, "IND_CNTD_ASS", DbType.String);
                        dataBase.SetParameterValue(command2, "IND_CNTD_ASS", signed ? "S" : "N");

                        command2.ExecuteNonQuery();
                        #endregion

                        #region Inserir registros na tabela de detalhes
                        command3.Transaction = trans;
                        command3.CommandText = " INSERT INTO TFWDET_LOG_AUDT";
                        command3.CommandText += " (COD_HEAD_LOG_AUDT,COD_DET_LOG_AUDT,NOME_DET_LOG,TXT_CNTD_DET_LOG)";
                        command3.CommandText += " VALUES (:COD_HEAD_LOG_AUDT,:COD_DET_LOG_AUDT,:NOME_DET_LOG,:TXT_CNTD_DET_LOG)";

                        dataBase.AddInParameter(command3, "COD_HEAD_LOG_AUDT", DbType.Int32);
                        dataBase.AddInParameter(command3, "COD_DET_LOG_AUDT", DbType.Int32);
                        dataBase.AddInParameter(command3, "NOME_DET_LOG", DbType.String);
                        dataBase.AddInParameter(command3, "TXT_CNTD_DET_LOG", DbType.String);

                        for (int i = 0; i < logEntry.DetailCount; i++)
                        {
                            dataBase.SetParameterValue(command3, "COD_HEAD_LOG_AUDT", codigoHeader);
                            dataBase.SetParameterValue(command3, "COD_DET_LOG_AUDT", i + 1);
                            dataBase.SetParameterValue(command3, "NOME_DET_LOG", logEntry.GetDetail(i).Key);
                            dataBase.SetParameterValue(command3, "TXT_CNTD_DET_LOG", logEntry.GetDetail(i).Value);

                            command3.ExecuteNonQuery();
                        }
                        #endregion

                        trans.Commit();
                    }
                    catch
                    {
                        if (trans != null)
                            trans.Rollback();

                        throw;
                    }
                    finally
                    {
                        if (command1 != null)
                            command1.Dispose();

                        if (command2 != null)
                            command2.Dispose();

                        if (command3 != null)
                            command3.Dispose();

                        if (trans != null)
                            trans.Dispose();
                    }
                }
            }
        }

        private int CreateOrRetrieveLogSubTypeId(OracleDataBase dataBase, DbConnection connection, DbTransaction trans, string logSubType)
        {
            DbCommand command1 = connection.CreateCommand();

            //verificar se o tipo de log ja existe
            command1.Transaction = trans;
            command1.CommandText = "SELECT COD_STIP_LOG FROM TFWSTIP_LOG WHERE DESC_STIP_LOG = :DESC_STIP_LOG";

            dataBase.AddInParameter(command1, "DESC_STIP_LOG", DbType.String);
            dataBase.SetParameterValue(command1, "DESC_STIP_LOG", logSubType.ToLower());

            object ret = command1.ExecuteScalar();
            if (ret != null && !(ret is DBNull))//existe o tipo
                return Convert.ToInt32(ret);
            else
            {
                //incluir o novo tipo de log

                DbCommand command2 = connection.CreateCommand();
                //recuperar chave atraves da sequence
                command2.Transaction = trans;
                command2.CommandText = "SELECT STFWSTIP_LOG.NEXTVAL FROM DUAL";

                int logSubTypeId = Convert.ToInt32(command2.ExecuteScalar());

                DbCommand command3 = connection.CreateCommand();
                //incluir novo tipo de log , forcando lowercase 
                command3.Transaction = trans;
                command3.CommandText = " INSERT INTO TFWSTIP_LOG";
                command3.CommandText += " (COD_STIP_LOG,DESC_STIP_LOG)";
                command3.CommandText += " VALUES (:COD_STIP_LOG,:DESC_STIP_LOG)";

                dataBase.AddInParameter(command3, "COD_STIP_LOG", DbType.Int32);
                dataBase.SetParameterValue(command3, "COD_STIP_LOG", logSubTypeId);
                dataBase.AddInParameter(command3, "DESC_STIP_LOG", DbType.String);
                dataBase.SetParameterValue(command3, "DESC_STIP_LOG", logSubType.ToLower());
                command3.ExecuteNonQuery();

                return logSubTypeId;
            }
        }

        private int CreateOrRetrieveLogTypeId(OracleDataBase dataBase, DbConnection connection, DbTransaction trans, string logType)
        {
            DbCommand command1 = connection.CreateCommand();

            //verificar se o tipo de log ja existe
            command1.Transaction = trans;
            command1.CommandText = "SELECT COD_TIPO_LOG FROM TFWTIPO_LOG WHERE DESC_TIPO_LOG = :DESC_TIPO_LOG";

            dataBase.AddInParameter(command1, "DESC_TIPO_LOG", DbType.String);
            dataBase.SetParameterValue(command1, "DESC_TIPO_LOG", logType.ToLower());

            object ret = command1.ExecuteScalar();
            if (ret != null && !(ret is DBNull))//existe o tipo
                return Convert.ToInt32(ret);
            else
            {
                //incluir o novo tipo de log

                DbCommand command2 = connection.CreateCommand();
                //recuperar chave atraves da sequence
                command2.Transaction = trans;
                command2.CommandText = "SELECT STFWTIPO_LOG.NEXTVAL FROM DUAL";

                int logTypeId = Convert.ToInt32(command2.ExecuteScalar());

                DbCommand command3 = connection.CreateCommand();
                //incluir novo tipo de log , forcando lowercase 
                command3.Transaction = trans;
                command3.CommandText = " INSERT INTO TFWTIPO_LOG";
                command3.CommandText += " (COD_TIPO_LOG,DESC_TIPO_LOG)";
                command3.CommandText += " VALUES (:COD_TIPO_LOG,:DESC_TIPO_LOG)";

                dataBase.AddInParameter(command3, "COD_TIPO_LOG", DbType.Int32);
                dataBase.SetParameterValue(command3, "COD_TIPO_LOG", logTypeId);
                dataBase.AddInParameter(command3, "DESC_TIPO_LOG", DbType.String);
                dataBase.SetParameterValue(command3, "DESC_TIPO_LOG", logType.ToLower());
                command3.ExecuteNonQuery();

                return logTypeId;
            }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
        }

        #endregion
    }
}
